package com.eorchis.module.department.dao.require;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.stereotype.Component;
import com.eorchis.constants.DeptConstants;
import com.eorchis.constants.RoleConstants;
import com.eorchis.core.basedao.dao.impl.HibernateAbstractBaseDao;
import com.eorchis.core.basedao.query.condition.IPagingQueryCondition;
import com.eorchis.core.basedao.query.condition.builder.IConditionBuilder;
import com.eorchis.core.basedao.query.condition.builder.impl.DefaultQueryConditionBuilder;
import com.eorchis.core.servicetemplate.treetemplate.jsonbean.jsonnodeparameters.UpdateOrderParameter;
import com.eorchis.module.department.domain.Department;
import com.eorchis.module.department.domain.DepartmentTreeCondition;
import com.eorchis.utils.utils.PropertyUtil;

@SuppressWarnings("unchecked")
@Component("com.eorchis.module.department.dao.require.DepartmentTreeRequire")
public class DepartmentTreeRequire extends HibernateAbstractBaseDao {
	public DepartmentTreeCondition isLeapNode(DepartmentTreeCondition condition) {
		StringBuffer hqlBuffer = new StringBuffer();
		hqlBuffer.append("select count(d.deptID) from Department d where d.activeState= ?");
		condition.addParameter(Department.IS_ACTIVE_Y);
		if ((condition.getNode() != null) && (!"".equals(condition.getNode()))) {
			hqlBuffer.append(" and d.parentID= ?");
			condition.addParameter(new String(condition.getNode()));
		}
		if (PropertyUtil.objectNotEmpty(condition.getSearchType()) && condition.getSearchType().equals(DeptConstants.DEPT_LEVEL_CODE_LV1)) {
			hqlBuffer.append(" and (length(d.treepath) - length(replace(d.treepath,'/','')) < 5 or d.treepath like ? ");
			condition.addParameter(new String("%/" + condition.getProjectId() + "/%"));
			hqlBuffer.append(" or d.parentID in ("+DeptConstants.GZW_DEP_PARENTID+") ");
			hqlBuffer.append(" or d.deptID in ("+DeptConstants.OTHER_LV1_DEPID+")) ");
		}
		if ((condition.getSearchDepID() != null) && (!"".equals(condition.getSearchDepID()))) {
			hqlBuffer.append(" and d.deptID= ?");
			condition.addParameter(condition.getSearchDepID());
		}
		if(RoleConstants.ROLE_CODE_GJC_ADMIN.equals(condition.getSearchRoleCode())){
			hqlBuffer.append(" and (length(d.treepath) - length(replace(d.treepath,'/','')) < 5 ");
			hqlBuffer.append(" or d.parentID in ("+DeptConstants.GZW_DEP_PARENTID+") ");
			hqlBuffer.append(" or d.deptID in ("+DeptConstants.OTHER_LV1_DEPID+") ");
			if(PropertyUtil.objectNotEmpty(condition.getSearchFrameType())&&condition.getSearchFrameType().equals("1")){
				hqlBuffer.append(" or d.parentID = "+DeptConstants.NOT_DEPID_CB);
			}
			hqlBuffer.append(" )");
		}
		hqlBuffer.append("  and (d.adminLevelCode<>? or d.adminLevelCode is null)  ");
		condition.addParameter(Department.DEPT_TYPE_THREE.toString());
		condition.setSql(hqlBuffer.toString());
		return condition;
	}

	public DepartmentTreeCondition updateTreeOrderNumHQL(UpdateOrderParameter updateOrderParameter) {
		DepartmentTreeCondition condition = new DepartmentTreeCondition();
		String hql = "";
		if ((updateOrderParameter.getUpObjID() != null) && (!"".equals(updateOrderParameter.getUpObjID())) && (updateOrderParameter.getUpOrderNumValue() != null)
				&& (!"".equals(updateOrderParameter.getUpOrderNumValue()))) {
			hql = "update Department d set d.orderNum=? where d.deptID=?";
			condition.addParameter(new Integer(updateOrderParameter.getUpOrderNumValue()));
			condition.addParameter(new Integer(updateOrderParameter.getUpObjID()));
		}
		condition.setSql(hql);
		return condition;
	}

	public DepartmentTreeCondition updateTreeParentIDHQL(DepartmentTreeCondition condition) {
		String hql = "";
		if ((condition.getNode() != null) && (!"".equals(condition.getNode())) && (condition.getParentNodeID() != null) && (!"".equals(condition.getParentNodeID()))) {
			hql = "update Department d set d.parentID=? where d.deptID=?";
			condition.addParameter(new Integer(condition.getParentNodeID()));
			condition.addParameter(new Integer(condition.getNode()));
		}
		condition.setSql(hql);
		return condition;
	}

	public DepartmentTreeCondition listDepartmentTreeHQL(DepartmentTreeCondition condition) {
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("select t from Department t where t.activeState = ?");
		condition.addParameter(Department.IS_ACTIVE_Y);
		if ((condition.getNode() != null) && (!"".equals(condition.getNode()))) {
			sqlBuffer.append(" and t.parentID= ?");
			condition.addParameter(new String(condition.getNode()));
		}
		if ((condition.getSearchDepName() != null) && (!"".equals(condition.getSearchDepName()))) {
			sqlBuffer.append(" and t.deptName like ?");
			condition.addParameter("%"+condition.getSearchDepName().trim()+"%");
		}
		if ((condition.getNoDeptID() != null) && (!"".equals(condition.getNoDeptID()))) {
			sqlBuffer.append(" and t.deptID not in (?");
			condition.addParameter(condition.getNoDeptID()[0]);
			for(int i=1;i<condition.getNoDeptID().length;i++){
				sqlBuffer.append(",?");
				condition.addParameter(condition.getNoDeptID()[i]);
			}
			sqlBuffer.append(") ");
		}
		if ((condition.getSearchDepID() != null) && (!"".equals(condition.getSearchDepID()))) {
			sqlBuffer.append(" and t.deptID= ?");
			condition.addParameter(condition.getSearchDepID());
		}
		sqlBuffer.append(DeptConstants.NOT_DEPID);
		if(RoleConstants.ROLE_CODE_GJC_ADMIN.equals(condition.getSearchRoleCode())){
			sqlBuffer.append(" and (length(t.treepath) - length(replace(t.treepath,'/','')) < 5 ");
			sqlBuffer.append(" or t.parentID in ("+DeptConstants.GZW_DEP_PARENTID+") ");
			sqlBuffer.append(" or t.deptID in ("+DeptConstants.OTHER_LV1_DEPID+") ");
			if(PropertyUtil.objectNotEmpty(condition.getSearchFrameType())&&condition.getSearchFrameType().equals("1")){
				sqlBuffer.append(" or t.parentID = "+DeptConstants.NOT_DEPID_CB);
			}
			sqlBuffer.append(" )");
		}
		if(PropertyUtil.objectNotEmpty(condition.getSearchTreeType())){
			sqlBuffer.append(" and t.deptID<> ?");
			condition.addParameter(DeptConstants.NOT_DEPID_CB);
		}
		sqlBuffer.append(" and t.deptID <> -4 and t.parentID <> -4 ");
		sqlBuffer.append(" and (t.adminLevelCode<>? or t.adminLevelCode is null)  order by t.orderNum");
		condition.addParameter(Department.DEPT_TYPE_THREE.toString());
		condition.setSql(sqlBuffer.toString());
		return condition;
	}
	
	public List<Department> listUnSeclectDepartmentTreeHQL(DepartmentTreeCondition condition) {
		StringBuffer sqlBuffer = new StringBuffer();
		Map<String, Object> params = new HashMap<String, Object>();
		sqlBuffer.append( "select t.DEPID as \"deptID\", " +
                      " t.ACTIVE_STATE as \"activeState\", " +
                      " t.admin_Level_Code as \"adminLevelCode\", " +
                      " t.DEPT_ADDRESS as \"deptAddress\", " +
                      " t.DEPCODE as \"deptCode\", " +
                      " t.DEPKIND_CODE as \"deptKindCode\", " +
                      " t.DEPNAME as \"deptName\", " +
                      " t.DEPT_PHONE as \"deptPhone\", " +
                      " t.DEPT_POST_NUM as \"deptPostNum\", " +
                      " t.ORDER_NUM as \"orderNum\", " +
                      " t.PARENT_ID as \"parentID\", " +
                      " t.treepath as \"treepath\" from BASE_DEPARTMENT t where t.active_State = :activeState");
		params.put("activeState", Department.IS_ACTIVE_Y);
		if ((condition.getSearchDepName() != null) && (!"".equals(condition.getSearchDepName()))) {
			sqlBuffer.append(" and t.depName like :depName");
			params.put("depName", "%"+condition.getSearchDepName().trim()+"%");
		}
//		if(!RoleConstants.ROLE_CODE_TWO_ADMIN.equals(condition.getSearchRoleCode())){
			if ((condition.getSearchTreepath() != null) && (!"".equals(condition.getSearchTreepath()))) {
				sqlBuffer.append(" and t.treepath like :treepath");
				params.put("treepath", condition.getSearchTreepath() + "%");
			}
			if ((condition.getSearchDepID() != null) && (!"".equals(condition.getSearchDepID()))) {
				sqlBuffer.append(" and t.DEPID =:depId");
				params.put("depId", condition.getSearchDepID());
			}
//		}
//		if ((condition.getNoDeptID() != null) && (!"".equals(condition.getNoDeptID()))) {
//			sqlBuffer.append(" and d.depID not in (?");
//			condition.addParameter(condition.getNoDeptID()[0]);
//			for(int i=1;i<condition.getNoDeptID().length;i++){
//				sqlBuffer.append(",?");
//				condition.addParameter(condition.getNoDeptID()[i]);
//			}
//			sqlBuffer.append(") ");
//		}
		sqlBuffer.append(DeptConstants.NOT_DEPID);
		sqlBuffer.append(" and t.depid <> -4 and t.parent_id <> -4 ");
		if(condition.getSearchFrameType()!=null&&condition.getSearchFrameType().equals("2")){
			sqlBuffer.append(" and t.depid<> :noDept ");
			params.put("noDept", DeptConstants.NOT_DEPID_CB);
		}
		if(RoleConstants.ROLE_CODE_TWO_ADMIN.equals(condition.getSearchRoleCode())&&PropertyUtil.objectNotEmpty(condition.getSearchTreepath())){
			sqlBuffer.append(" and  t.admin_Level_Code =2 ");
			params.put("treepath", condition.getSearchTreepath()+"%");
		}else{
			if(DeptConstants.DEPT_LEVEL_CODE_LV1.equals(condition.getSearchType())){
				sqlBuffer.append(" and (length(t.treepath) - length(replace(t.treepath,'/','')) < 5 or t.treepath like :treepath2 ");
				params.put("treepath2", new String("%/" + condition.getProjectId() + "/%"));
				sqlBuffer.append(" or t.parent_ID in ("+DeptConstants.GZW_DEP_PARENTID+") ");
				sqlBuffer.append(" or t.depID in ("+DeptConstants.OTHER_LV1_DEPID+")) ");
			}
			if(RoleConstants.ROLE_CODE_GJC_ADMIN.equals(condition.getSearchRoleCode())){
				sqlBuffer.append(" and (length(t.treepath) - length(replace(t.treepath,'/','')) < 5 ");
				sqlBuffer.append(" or t.parent_ID in ("+DeptConstants.GZW_DEP_PARENTID+") ");
				sqlBuffer.append(" or t.depID in ("+DeptConstants.OTHER_LV1_DEPID+") ");
				if(PropertyUtil.objectNotEmpty(condition.getSearchFrameType())&&condition.getSearchFrameType().equals("1")){
					sqlBuffer.append(" or t.parent_id = "+DeptConstants.NOT_DEPID_CB);
				}
				sqlBuffer.append(" )");
			}
			sqlBuffer.append(" and (t.admin_Level_Code<> :adminLevelCode or t.admin_Level_Code is null)  order by  t.order_Num ");
			params.put("adminLevelCode", Department.DEPT_TYPE_THREE.toString());
		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(sqlBuffer.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
 		Long count = this.count(queryCondition);
		condition.setCountPage(count);
		
//		sql = sql + sqlBuffer.toString() + ") row_ wherem rownum <= "+condition.getStartPage()+") t2 where rownum_ > "+condition.getEndPage();
		IConditionBuilder defaultQueryConditionBuilder2 = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder2.setBaseQueryString(sqlBuffer.toString());
		defaultQueryConditionBuilder2.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder2.setResultObject(Department.class);
		IPagingQueryCondition queryCondition2 = (IPagingQueryCondition) defaultQueryConditionBuilder2.buliderQueryCondition();
		queryCondition2.setParameters(params);
		List<Department> list = (List<Department>) this.findList(queryCondition2,condition.getEndPage(), condition.getStartPage()-condition.getEndPage());
		return list;
	}

	public DepartmentTreeCondition listDepartmentTreeHQLForLv1(DepartmentTreeCondition condition) {
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("select t from Department t where t.activeState = ?");
		condition.addParameter(Department.IS_ACTIVE_Y);
		if ((condition.getNode() != null) && (!"".equals(condition.getNode()))) {
			sqlBuffer.append(" and t.parentID= ?");
			condition.addParameter(new String(condition.getNode()));
		}
		if ((condition.getSearchDepName() != null) && (!"".equals(condition.getSearchDepName()))) {
			sqlBuffer.append(" and t.deptName like ?");
			condition.addParameter("%"+condition.getSearchDepName().trim()+"%");
		}
		if ((condition.getNoDeptID() != null) && (!"".equals(condition.getNoDeptID()))) {
			sqlBuffer.append(" and t.deptID not in (?");
			condition.addParameter(condition.getNoDeptID()[0]);
			for(int i=1;i<condition.getNoDeptID().length;i++){
				sqlBuffer.append(",?");
				condition.addParameter(condition.getNoDeptID()[i]);
			}
			sqlBuffer.append(") ");
		}
//		if(PropertyUtil.objectNotEmpty(condition.getSearchTreeType())){
			sqlBuffer.append(" and t.deptID<> ?");
			condition.addParameter(DeptConstants.NOT_DEPID_CB);
//		}
		sqlBuffer.append(DeptConstants.NOT_DEPID);
		sqlBuffer.append(" and (length(t.treepath) - length(replace(t.treepath,'/','')) < 5 or t.treepath like ? ");
		condition.addParameter(new String("%/" + condition.getProjectId() + "/%"));
		sqlBuffer.append(" or t.parentID in ("+DeptConstants.GZW_DEP_PARENTID+") ");
		sqlBuffer.append(" or t.deptID in ("+DeptConstants.OTHER_LV1_DEPID+")) ");
		sqlBuffer.append(" and (t.adminLevelCode<>? or t.adminLevelCode is null) order by t.orderNum");
		condition.addParameter(Department.DEPT_TYPE_THREE.toString());
		condition.setSql(sqlBuffer.toString());
		return condition;
	}

	public DepartmentTreeCondition listDepartmentTreeHQL1(DepartmentTreeCondition condition) {
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("select t from Department t where t.activeState = ?");
		condition.addParameter(Department.IS_ACTIVE_Y);
		if ((condition.getNode() != null) && (!"".equals(condition.getNode()))) {
			sqlBuffer.append(" and t.parentID= ?");
			condition.addParameter(new String(condition.getNode()));
		}
		sqlBuffer.append("  order by t.orderNum");
		condition.setSql(sqlBuffer.toString());
		return condition;
	}

	public DepartmentTreeCondition listDepartmentCheckedTreeHQL(DepartmentTreeCondition condition) {
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("select t.deptID as deptID,t.deptName as deptName,t.belongToRegion as belongToRegion,t.deptCode as deptCode,t.deptAddress as deptAddress,");
		sqlBuffer.append(" t.deptPostNum as deptPostNum,t.deptPhone as deptPhone,t.orderNum as orderNum,t.deptKindCode as deptKindCode,t.treepath as treepath,");
		sqlBuffer.append(" t.parentID as parentID,t.reportDeptID as reportDeptID,t.activeState as activeState");

		if ((condition.getSearchProjectID() != null) && (!condition.getSearchProjectID().equals(""))) {
			sqlBuffer.append(",case when t.deptID in (select j.company.deptID from Projectcom j where j.projectInfo.projectId=?) then '1'");
			condition.addParameter(condition.getSearchProjectID());
			sqlBuffer.append(" when t.deptID not in(select j.company.deptID from Projectcom j where j.projectInfo.projectId=?) then '0' end as isChecked");
			condition.addParameter(condition.getSearchProjectID());
		}
		sqlBuffer.append(" from Department t where t.activeState = ?");
		condition.addParameter(Department.IS_ACTIVE_Y);
		if ((condition.getNode() != null) && (!"".equals(condition.getNode()))) {
			sqlBuffer.append(" and t.parentID= ?");
			condition.addParameter(new String(condition.getNode()));
		}

		if ((condition.getProjectId() != null) && (!condition.getProjectId().equals(""))) {
			sqlBuffer.append(" and t.deptID in (select j.company.deptID from Projectcom j where j.projectInfo.projectId=?)");
			condition.addParameter(condition.getProjectId());
		}
		sqlBuffer.append(" order by t.orderNum");
		condition.setSql(sqlBuffer.toString());
		return condition;
	}

	public DepartmentTreeCondition listDepartmentProjectTreeHQL(DepartmentTreeCondition condition) {
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("select t from Department t where t.activeState = ?");
		condition.addParameter(Department.IS_ACTIVE_Y);

		if ((condition.getSearchProjectID() != null) && (!condition.getSearchProjectID().equals(""))) {
			sqlBuffer.append(" and t.deptID in (select j.company.deptID from Projectcom j where j.projectInfo.projectId = ? ");
			condition.addParameter(condition.getSearchProjectID());
			if (condition.getSearchStationLeaderID() != null) {
				sqlBuffer.append(" and j.projectcomid in (select tt.projectcom.projectcomid from Prostaleaderrelation tt");
				sqlBuffer.append(" where tt.user.userId = ?");
				condition.addParameter(condition.getSearchStationLeaderID());
				sqlBuffer.append(")");
			} else if (condition.getSearchAuditLeaderID() != null) {
				sqlBuffer.append(" and j.company.deptID in (select tt.choosePhase.stationInfo.company.deptID from ChoosePhaseManager tt");
				sqlBuffer.append(" where tt.choosePhase.phasetypeCode.dataCode = ?");
				condition.addParameter("SH");
				sqlBuffer.append(" and tt.user.userId = ?");
				condition.addParameter(condition.getSearchAuditLeaderID());
				sqlBuffer.append(")");
			}
			sqlBuffer.append(")");
		}

		if ((condition.getSearchTreepath() != null) && (!"".equals(condition.getSearchTreepath()))) {
			sqlBuffer.append(" and t.treepath like ?");
			condition.addParameter(condition.getSearchTreepath() + "%");
		}

		sqlBuffer.append(" order by t.orderNum");
		condition.setSql(sqlBuffer.toString());
		return condition;
	}
}